from PyQt5.QtWidgets import (
    QWidget,QDesktopWidget, QPushButton, QLabel, QProgressBar, QFileDialog, QApplication,QProgressDialog,QMessageBox)
from PyQt5.QtCore import Qt,QBasicTimer,QThread, pyqtSignal
import pandas as pd
import pymysql
from sqlalchemy import create_engine
import sys,math,time

class Example(QWidget):

    def __init__(self):
        super().__init__()
        self.initUI()

    def initUI(self):

        # 读取文件目录
        getBtn = QPushButton('打开', self)
        getBtn.setCheckable(False)
        getBtn.move(10, 10)
        getBtn.clicked[bool].connect(self.getpath)
        # 显示文件目录
        self.label = QLabel(self)
        self.label.setGeometry(20, 60, 460, 30)
        self.label.setText('请选择 excel 格式文件')
        # 显示导入按钮
        self.importBtn = QPushButton('准备数据', self)
        self.importBtn.setCheckable(False)
        self.importBtn.move(80, 10)
        self.importBtn.clicked[bool].connect(self.importfile)

        # 显示窗口
        self.resize(500, 300)
        self.center()
        self.setWindowTitle('每日电表数据导入')
        self.show()

    # 取地址
    def getpath(self):
        filename,_filter = QFileDialog.getOpenFileName(self,'选择文件','','Excel files(*.xlsx , *.xls)')
        self.label.setText(filename)
        print(filename)
        # 绑定导入功能
        self.importBtn.setEnabled(True);
        self.importBtn.setText('开始导入')
        self.importBtn.clicked[bool].connect(self.importfile)

    # 导入
    def importfile(self):
        # 取消绑定的导入事件
        self.importBtn.setText('正在导入')
        self.importBtn.setEnabled(False);
        self.importBtn.clicked[bool].disconnect()
        # 开始读文件
        IO = self.label.text()
        # IO = '/data/coding-learn/python/pyqt/1.xlsx'
        sheet = pd.read_excel(io=IO)
        df = pd.DataFrame(sheet)
        count = df.count().CONS_NAME
        # 进度条
        progress = QProgressDialog(self)
        progress.setWindowTitle("请稍等")  
        progress.setLabelText("正在操作...")
        progress.setCancelButtonText("取消")
        progress.setMinimumDuration(10)
        progress.setWindowModality(Qt.WindowModal)
        progress.setRange(0,count)
        # 每1%进度需要插入多少条
        j = math.ceil(count/100)
        print('j',j)
        # 打开数据库连接
        db = pymysql.connect("47.96.13.76", "root", "Sx3jdCwhCZAmxWie", "python_cs", charset='utf8mb4')
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
        values = []
        # 总数
        i = 1
        k = 1
        for index,row in df.iterrows():
            print('k',k)
            progress.setValue(index)
            if progress.wasCanceled():
                QMessageBox.warning(self,"提示","操作失败") 
                break
            print(index,row.CONS_NAME)
            value = (row.CONS_NAME, row.CONS_NO, row.SEND_DATE.strftime('%Y-%m-%d %H:%M:%S'), row.T_AMT, row.T_CAT_KWH_AMT, row.T_PL_AMT, row.T_SETTLE_PQ, row.YM)
            values.append(value)
            # 每 500 条插入一次
            if i >= j or k >= count:
                sql = "INSERT INTO dlsj_x(CONS_NAME, CONS_NO, SEND_DATE, T_AMT, T_CAT_KWH_AMT, T_PL_AMT, T_SETTLE_PQ, YM) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
                try:
                    # 执行sql语句
                    cursor.executemany(sql,values)
                    # 提交到数据库执行
                    db.commit()
                    # print(True)
                    # 清空数组与计数
                    values = []
                    i = 0
                except Exception as err:
                    # Rollback in case there is any error
                    # db.rollback()
                    print("Error %s for execute sql: %s" % (err, sql))
            else:
                i = i + 1
            k = k + 1
        else:
            progress.setValue(count)
            QMessageBox.information(self,"提示","操作成功")
        # 关闭数据库连接
        db.close()
        


    def onCountChanged(self, value):
        self.pbar.setValue(value)

    # 居中
    def center(self):
        qr = self.frameGeometry()
        cp = QDesktopWidget().availableGeometry().center()
        qr.moveCenter(cp)
        self.move(qr.topLeft())


if __name__ == '__main__':

    app = QApplication(sys.argv)
    ex = Example()
    sys.exit(app.exec_())
